/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package br.com.bancofloripa.dao;

import br.com.bancofloripa.entity.Agencia;
import br.com.bancofloripa.entity.Cliente;
import br.com.bancofloripa.entity.ContaOuro;
import br.com.bancofloripa.exception.MensagemExceptionDAO;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author fabianoc
 */
public class ContaOuroDAO  extends BaseDao {
    
   public int insere(ContaOuro cl) throws MensagemExceptionDAO {
       int id = 0;
        Connection c = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        int i = 0;
        try {
            StringBuilder sb = new StringBuilder();
            c = super.getConexaoBD();
            sb.append("INSERT INTO conta ");
            sb.append("(idagencia, numero, saldo, idcliente, limite, tipo) values (?, ?, ?, ?, ?, ?)");
            ps = c.prepareStatement(sb.toString(), ps.RETURN_GENERATED_KEYS);
            ps.setInt(++i, cl.getAgencia().getId());
            ps.setInt(++i, cl.getnConta());
            ps.setDouble(++i, cl.getSaldo());
            ps.setInt(++i, cl.getCliente().getId()); 
            ps.setDouble(++i, cl.getLimite());
            ps.setString(++i, cl.getTipo());
            ps.execute();
            rs = ps.getGeneratedKeys();
            rs.next();
            id = rs.getInt(1);
        } catch (SQLException ex) {
            throw new MensagemExceptionDAO("Erro de sql "+ex.getMessage());
        } catch (MensagemExceptionDAO ex) {
            ex.getMessage();
        }        
        return id;
    }

    public void atualiza(ContaOuro ct) throws MensagemExceptionDAO {
        Connection c = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        StringBuilder sb = new StringBuilder();
        int i = 0;
        try {
            c = super.getConexaoBD();
            sb.append("UPDATE conta SET ");
            sb.append("idagencia = ?, numero = ?, saldo = ?, idcliente = ?, limite = ?, "
                    + "tipo = ? WHERE id = ?");
            ps = c.prepareStatement(sb.toString());
            ps.setInt(++i, ct.getAgencia().getId());
            ps.setInt(++i, ct.getnConta());
            ps.setDouble(++i, ct.getSaldo());
            ps.setInt(++i, ct.getCliente().getId());
            ps.setDouble(++i, ct.getLimite());
            ps.setString(++i, ct.getTipo());
            ps.setInt(++i, ct.getId());
            ps.execute();
        } catch (SQLException ex) {
            throw new MensagemExceptionDAO("Erro de sql "+ex.getMessage());
        } catch (MensagemExceptionDAO ex) {
            ex.getMessage();
        }   finally {
            this.closeConnection(ps, c);
        }      
    }

    public List<ContaOuro> getAll() {

       List<ContaOuro> lista = new ArrayList<ContaOuro>();

       PreparedStatement ps = null;
       Connection c = null ;
       ResultSet rs = null;
       try {
            c = super.getConexaoBD();
            ps = c.prepareStatement("SELECT c.id, c.idagencia, c.numero, "
                    +"c.saldo, c.idcliente, c.limite, cl.nome as  cliente, "
                    + "a.nome as agencia FROM conta as c LEFT JOIN agencia as a " 
                    +"on c.idagencia = a.id " 
                    +"LEFT JOIN cliente as cl on c.idcliente = cl.id "
                    + "where c.tipo like '%Ouro%'"
                    );
            rs = ps.executeQuery();
            while ( rs.next() == true ) {
                
                Agencia agencia = new Agencia( rs.getInt("idagencia") );
                Cliente cliente = new Cliente ( rs.getInt("idcliente") );
                cliente.setNome( rs.getString("cliente") );
                agencia.setNome(rs.getString("agencia"));
                
                
                ContaOuro cl = new ContaOuro( rs.getDouble("limite"), rs.getInt("id"), agencia, 
                        rs.getInt("numero"), rs.getDouble("saldo"),
                        cliente );
                lista.add( cl );
            }
        } catch (MensagemExceptionDAO ex) {
            Logger.getLogger(ContaOuroDAO.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            this.closeConnection(ps, c);
       }
       return lista;
    }

    public List<ContaOuro> getByFilter(String filtro) {
       List<ContaOuro> lista = new ArrayList<ContaOuro>();

       PreparedStatement ps = null;
       Connection c = null ;
       ResultSet rs = null;
       int i = 0;
       try {
            c = super.getConexaoBD();
            ps = c.prepareStatement("SELECT c.id, c.idagencia, c.numero, "
                    +"c.saldo, c.idcliente, c.limite, cl.nome as cliente, cl.cpf as cpf, "
                    + "a.nome as agencia FROM conta as c LEFT JOIN agencia as a " 
                    +"on c.idagencia = a.id " 
                    +"LEFT JOIN cliente as cl on c.idcliente = cl.id "
                    +"where cl.nome like ? and c.tipo like '%Ouro%'"
                    );
            filtro = "%"+ filtro + "%";
            ps.setString(++i, filtro);
            rs = ps.executeQuery();
            while ( rs.next() == true ) {
                
                Agencia agencia = new Agencia( rs.getInt("idagencia") );
                Cliente cliente = new Cliente ( rs.getInt("idcliente") );
                cliente.setNome( rs.getString("cliente") );
                cliente.setCpf( rs.getString("cpf") );
                agencia.setNome(rs.getString("agencia"));
                
                
                ContaOuro cl = new ContaOuro( rs.getDouble("limite"), rs.getInt("id"), 
                        agencia, rs.getInt("numero"), rs.getDouble("saldo"),
                        cliente );
                lista.add( cl );
            }
        } catch (MensagemExceptionDAO ex) {
            Logger.getLogger(ContaOuroDAO.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
         finally {
            this.closeConnection(ps, c);
       }
       return lista;
    }

    public ContaOuro getById(int conta) {
       ContaOuro cl = null;

       PreparedStatement ps = null;
       Connection c = null ;
       ResultSet rs = null;
       int i = 0;
       try {
            c = super.getConexaoBD();
            ps = c.prepareStatement("SELECT c.id, c.idagencia, c.numero, "
                    +"c.saldo, c.idcliente, c.limite, cl.nome as  cliente, "
                    + "a.nome as agencia FROM conta as c LEFT JOIN agencia as a " 
                    +"on c.idagencia = a.id " 
                    +"LEFT JOIN cliente as cl on c.idcliente = cl.id "
                    +"where c.numero = ?  and c.tipo like '%Ouro%'"
                    );
            ps.setInt(++i, conta);
            rs = ps.executeQuery();
            while ( rs.next() == true ) {
                
                Agencia agencia = new Agencia( rs.getInt("idagencia") );
                Cliente cliente = new Cliente ( rs.getInt("idcliente") );
                cliente.setNome( rs.getString("cliente") );
                agencia.setNome(rs.getString("agencia"));
                
                
                cl = new ContaOuro( rs.getDouble("limite"), rs.getInt("id"), 
                        agencia, rs.getInt("numero"), rs.getDouble("saldo"),
                        cliente );
            }
        } catch (MensagemExceptionDAO ex) {
            Logger.getLogger(ContaOuroDAO.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
         finally {
            this.closeConnection(ps, c);
       }
       return cl;
    }  
    
    public static void main(String[] args) {
        try {
            Agencia ag = new Agencia("noroeste");
            ClienteDAO cld = new ClienteDAO();
            List<Cliente> cl = cld.getAll();
            
            ContaOuroDAO cd = new ContaOuroDAO();
            ContaOuro cc = new ContaOuro(200.00, ag, 12313, 333.00, cl.get(2));
            System.out.println("id inserido "+ cd.insere(cc));
            String nome = cl.get(1).getNome();
            //System.out.println(nome);
            for (int i = 0; i < cd.getByFilter(nome).size(); i++) {
                System.out.println("Lista :"+cd.getByFilter(nome).get(i).getnConta());
            }
            
        } catch (MensagemExceptionDAO ex) {
            ex.printStackTrace();
        }
    }
        
}
 